package com.pengcheng.hnweb.job;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.pengcheng.hnweb.service.DbService;
import com.pengcheng.hnweb.util.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.web.client.RestTemplate;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.*;
import java.nio.file.attribute.BasicFileAttributes;
import java.util.*;

@Slf4j
@Component
public class TestJob{

    @Value("${spring.redis.expire}")
    private int expire;
    @Value("${remote.ip}")
    private String ip;
    @Value("${remote.port}")
    private int port;
    @Autowired
    private RestTemplate restTemplate;
    @Autowired
    private RabbitTemplate rabbitTemplate;
    @Autowired
    private DbService dbService;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private JsonUtil jsonUtil;
    @Autowired
    private RedisUtil redisUtil;
    @Autowired
    private RabbitUtil rabbitUtil;
    @Autowired
    private Common common;
    @Autowired
    private FileUtil fileUtil;
    @Autowired
    private DsendUtil dsendUtil;
    @Autowired
    private SocketUtil socketUtil;
    @Scheduled(cron = "*/5 * * * * ?")
    public void run() {

    }
    public void update_unix_time() {
        //查询没有sync的device
        String sql="select t1.* from hnww_device as t1 left join hnww_synctime as t2 on t1.id=t2.device_id where t1.deleted=0 and t1.state=1 and t2.id is null order by t1.id asc";
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        Map<String,Object> row_device=null;
        if(rows_device!=null&&rows_device.size()>0)
        {
            row_device=rows_device.get(0);
        }
        if(row_device==null)
        {
            sql="select t1.* from hnww_device as t1 left join hnww_synctime as t2 on t1.id=t2.device_id where t1.deleted=0 and t1.state=1 and t2.id is not null order by t2.fdate asc";
            rows_device=jdbcTemplate.queryForList(sql);
            if(rows_device!=null&&rows_device.size()>0)
            {
                row_device=rows_device.get(0);
            }
        }
        if(row_device!=null)
        {
            String msg= common.get_time_msg(new Date());
            String channel_id=row_device.get("channel_id").toString();
            Map<String,Object> res=new HashMap<>();
            res.put("channel_id",channel_id);
            res.put("msg",msg);
            rabbitTemplate.convertAndSend("hnww.respon",res);
            sql="select * from hnww_synctime where device_id="+row_device.get("id").toString();
            List<Map<String,Object>> rows_synctime=jdbcTemplate.queryForList(sql);
            if(rows_synctime==null||rows_synctime.size()==0)
            {
                sql="insert into hnww_synctime (device_id,fdate,deleted) values (?,?,0)";
                Object[] ps1={row_device.get("id").toString(),common.format_date(new Date())};
                jdbcTemplate.update(sql,ps1);
            }
            else {
                sql="update hnww_synctime set fdate=? where id="+rows_synctime.get(0).get("id").toString();
                Object[] ps2={common.format_date(new Date())};
                jdbcTemplate.update(sql,ps2);
            }
        }
    }
    //定时更新dlogcheck
//    @Scheduled(cron = "0 10 * * * ?")
    public void update_dlogcheck()
    {
        //清空dlogcheck
        String sql="TRUNCATE TABLE hnww_dlogcheck";
        jdbcTemplate.update(sql);
        sql="insert into hnww_dlogcheck (device_id,dyear,dmonth,min_ddate,dlog_id,flow) select b1.device_id,b1.dyear,b1.dmonth,b1.min_ddate,b1.dlog_id,b1.flow FROM (select t1.*,t2.id as dlog_id,t2.flow from (select device_id,dyear,dmonth,MIN(ddate) as min_ddate from hnww_dlog where deleted=0 group by device_id,dyear,dmonth) as t1 left join hnww_dlog as t2 on t1.device_id=t2.device_id and t1.min_ddate=t2.ddate) as b1";
        jdbcTemplate.update(sql);
    }
    //定时更新dlogcheck
    @Scheduled(fixedDelay = 10000)
    public void check_repeat()
    {
        String sql="";
        try {
            sql="select id from "+ dbService.prefix+"_device where deleted=0 order by id asc";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            for(int ii=0;ii<rows_device.size();ii++)
            {
                int device_id=Integer.parseInt(rows_device.get(ii).get("id")+"");
                do_check_repeat(device_id);
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("check_repeat",common.format_exception(ex));
        }
    }
    public void do_check_repeat(int device_id) throws Exception
    {
        try {
            String sql = "";
            //获取fday,fmonth,fyear
            sql = "select fhour,fcount from (select left(ddate,13) as fhour,count(id) as fcount from hnww_dlog"+device_id+" group by left(ddate,13)) as t1 where t1.fcount>1";
            List<Map<String, Object>> rows_fhour = jdbcTemplate.queryForList(sql);
            for (int i = 0; i < rows_fhour.size(); i++) {
                String fhour = rows_fhour.get(i).get("fhour") + "";
                sql = "select * from hnww_dlog" + device_id + " where deleted=0 and device_id=" + device_id + " and left(ddate,13)='" + fhour + "' order by ddate desc,id desc limit 0,1";
                List<Map<String, Object>> rows_dlog2 = jdbcTemplate.queryForList(sql);
                if (rows_dlog2 != null && rows_dlog2.size() > 0) {
                    JSONObject row_dlog2 = JSONObject.parseObject(JSONObject.toJSONString(rows_dlog2.get(0)));
                    int id2 = jsonUtil.get_int_by_path(row_dlog2.toJSONString(), "$.id");
                    //进行更新
                    sql = "delete from hnww_dlog"+device_id+" where deleted=0  and left(ddate,13)='" + fhour + "' and id!=?";
                    jdbcTemplate.update(sql,new Object[]{id2});
                }
            }
            fileUtil.add_msg("check_repeat", "更新完成:device_id:" + device_id);
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("check_repeat","device_id:"+common.format_exception(ex));
        }
    }
    //定时更新dlogcheck
    @Scheduled(fixedDelay = 60000)
    public void update_dloglast()
    {
        String sql="";
        try {
            sql="select id from "+ dbService.prefix+"_device where deleted=0 order by id asc";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            for(int ii=0;ii<rows_device.size();ii++)
            {
                int device_id=Integer.parseInt(rows_device.get(ii).get("id")+"");
                do_update_dloglast(device_id);
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_dlogcheck",common.format_exception(ex));
        }
    }

    public void do_update_dloglast(int device_id) throws Exception
    {
        String sql = "";
        try {
            //获取fday,fmonth,fyear
            sql = "select distinct(left(ddate,7)) as fmonth from hnww_dlog" + device_id + " where (last_of_day=-1 or last_of_month=-1) and deleted=0";
            List<Map<String, Object>> rows_fmonth = jdbcTemplate.queryForList(sql);
            sql = "select distinct(left(ddate,10)) as fday from hnww_dlog" + device_id + " where (last_of_day=-1 or last_of_month=-1) and deleted=0";
            List<Map<String, Object>> rows_fday = jdbcTemplate.queryForList(sql);
            for (int i = 0; i < rows_fmonth.size(); i++) {
                String fmonth = rows_fmonth.get(i).get("fmonth") + "";
                sql = "select * from hnww_dlog" + device_id + " where deleted=0 and device_id=" + device_id + " and left(ddate,7)='" + fmonth + "' order by ddate desc,id desc limit 0,1";
                List<Map<String, Object>> rows_dlog2 = jdbcTemplate.queryForList(sql);
                if (rows_dlog2 != null && rows_dlog2.size() > 0) {
                    JSONObject row_dlog2 = JSONObject.parseObject(JSONObject.toJSONString(rows_dlog2.get(0)));
                    int id2 = jsonUtil.get_int_by_path(row_dlog2.toJSONString(), "$.id");
                    //进行更新
                    sql = "update hnww_dlog" + device_id + " set last_of_month=0 where last_of_month!=0 and device_id=" + device_id + " and left(ddate,7)='" + fmonth + "'";
                    jdbcTemplate.update(sql);
                    sql = "update hnww_dlog" + device_id + " set last_of_month=1 where id=" + id2;
                    jdbcTemplate.update(sql);
                }
            }
            for (int i = 0; i < rows_fday.size(); i++) {
                String fday = rows_fday.get(i).get("fday") + "";
                sql = "select * from hnww_dlog" + device_id + " where deleted=0 and device_id=" + device_id + " and left(ddate,10)='" + fday + "' order by ddate desc,id desc limit 0,1";
                List<Map<String, Object>> rows_dlog2 = jdbcTemplate.queryForList(sql);
                if (rows_dlog2 != null && rows_dlog2.size() > 0) {
                    JSONObject row_dlog2 = JSONObject.parseObject(JSONObject.toJSONString(rows_dlog2.get(0)));
                    int id2 = jsonUtil.get_int_by_path(row_dlog2.toJSONString(), "$.id");
                    //进行更新
                    sql = "update hnww_dlog" + device_id + " set last_of_day=0 where last_of_day!=0 and device_id=" + device_id + " and left(ddate,10)='" + fday + "'";
                    jdbcTemplate.update(sql);
                    sql = "update hnww_dlog" + device_id + " set last_of_day=1 where id=" + id2;
                    jdbcTemplate.update(sql);
                }
            }
            //更新dloglast
//            sql = "SELECT * FROM hnww_dlog" + device_id + " WHERE deleted=0 order by ddate desc limit 0,1";
//            List<Map<String, Object>> list_dlog = jdbcTemplate.queryForList(sql);
//            if (list_dlog.size() > 0 && list_dlog.get(0).get("id") != null) {
//                int id_dlog = Integer.parseInt(list_dlog.get(0).get("id") + "");
//                sql="select * from hnww_dloglast where deleted=0 and device_id=?";
//                List<Map<String, Object>> list_dlog2 = jdbcTemplate.queryForList(sql,new Object[]{device_id});
//                if(list_dlog2.size()>0&&list_dlog2.get(0).get("id")!=null)
//                {
//                    int id_dlog2=Integer.parseInt(list_dlog2.get(0).get("id") + "");
//                    if(id_dlog2!=id_dlog)
//                    {
//                        sql = "delete from hnww_dloglast where device_id=?";
//                        jdbcTemplate.update(sql, new Object[]{device_id});
//                        sql = "INSERT INTO hnww_dloglast SELECT * FROM hnww_dlog" + device_id + " WHERE deleted=0 order by ddate desc limit 0,1";
//                        jdbcTemplate.update(sql);
//                    }
//                }
//                else {
//                    sql = "INSERT INTO hnww_dloglast SELECT * FROM hnww_dlog" + device_id + " WHERE deleted=0 order by ddate desc limit 0,1";
//                    jdbcTemplate.update(sql);
//                }
//            }
            //删除dloglast里面多余的
            sql="select * from "+ dbService.prefix+"_dloglast where device_id=? order by ddate desc,id desc";
            List<Map<String, Object>> list_dlog = jdbcTemplate.queryForList(sql,new Object[]{device_id});
            if(list_dlog.size()>1)
            {
                int id_dlog=Integer.parseInt(list_dlog.get(0).get("id")+"");
                sql="delete from "+ dbService.prefix+"_dloglast where device_id=? and id!=?";
                jdbcTemplate.update(sql,new Object[]{device_id,id_dlog});
            }
            fileUtil.add_msg("update_dlogcheck", "更新完成:device_id:" + device_id);
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_dlogcheck", "发生异常:device_id:" + common.format_exception(ex));
        }
    }
    //定时更新dlogcheck
    @Scheduled(fixedDelay = 1800000)
    public void update_flow()
    {
        String sql="";
        //清空dlogcheck
        try {
            sql="select id from "+ dbService.prefix+"_device where deleted=0 order by id asc";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            for(int ii=0;ii<rows_device.size();ii++) {
                int device_id = Integer.parseInt(rows_device.get(ii).get("id") + "");
                do_update_flow(device_id);
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_flow",common.format_exception(ex));
        }
    }
    public void do_update_flow(int device_id) throws Exception
    {
        String sql="";
        sql="select * from hnww_dlog"+device_id+" where (last_of_day=1) and deleted=0 and id not in (select dlog_id1 from "+ dbService.prefix+"_dayflow1 where device_id="+device_id+") and id not in (select dlog_id2 from "+ dbService.prefix+"_dayflow1 where device_id="+device_id+") order by id asc";
        List<Map<String,Object>> list_dlog1=jdbcTemplate.queryForList(sql);
        if(list_dlog1.size()>0)
        {
            for(int i=0;i<list_dlog1.size();i++)
            {
                JSONObject row_dlog=JSONObject.parseObject(JSONObject.toJSONString(list_dlog1.get(i)));
                int dday=jsonUtil.get_int_by_path(row_dlog.toJSONString(),"$.dday");
                int dmonth=jsonUtil.get_int_by_path(row_dlog.toJSONString(),"$.dmonth");
                int dyear=jsonUtil.get_int_by_path(row_dlog.toJSONString(),"$.dyear");
                update_dayflow(device_id,dyear,dmonth,dday);
            }
        }
        sql="select * from hnww_dlog"+device_id+" where (last_of_month=1) and deleted=0 and id not in (select dlog_id1 from "+ dbService.prefix+"_monthflow1 where device_id="+device_id+") and id not in (select dlog_id2 from "+ dbService.prefix+"_monthflow1 where device_id="+device_id+") order by id asc";
        List<Map<String,Object>> list_dlog2=jdbcTemplate.queryForList(sql);
        if(list_dlog2.size()>0)
        {
            for(int i=0;i<list_dlog2.size();i++)
            {
                JSONObject row_dlog=JSONObject.parseObject(JSONObject.toJSONString(list_dlog2.get(i)));
                int dday=jsonUtil.get_int_by_path(row_dlog.toJSONString(),"$.dday");
                int dmonth=jsonUtil.get_int_by_path(row_dlog.toJSONString(),"$.dmonth");
                int dyear=jsonUtil.get_int_by_path(row_dlog.toJSONString(),"$.dyear");
                update_monthflow(device_id,dyear,dmonth);
            }
        }
        //年度
        update_yearflow(device_id);
        fileUtil.add_msg("update_flow","device_id:"+device_id);
    }
    public void update_dayflow(int device_id,int dyear,int dmonth,int dday)
    {
        try {
            String sql="";
            List<Map<String,Object>> columns_dayflow1=dbService.columns("dayflow1");
            Date now=common.make_date(dyear,dmonth,dday);
            Date last_day=common.parse_date(common.add_day(now,-1));
            Date next_day=common.parse_date(common.add_day(now,1));
            //查询当天的row_dlog
            sql="select * from hnww_dlog"+device_id+" where last_of_day=1 and deleted=0 and dyear=? and dmonth=? and dday=?";
            List<Map<String,Object>> list_dlog_now=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(now),common.get_month(now),common.get_day(now)});
            List<Map<String,Object>> list_dlog_last=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(last_day),common.get_month(last_day),common.get_day(last_day)});
            List<Map<String,Object>> list_dlog_next=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(next_day),common.get_month(next_day),common.get_day(next_day)});
            if(list_dlog_now.size()==0)
            {
                throw new RuntimeException("当前日期的最新记录为空");
            }
            if(list_dlog_last.size()==1)
            {
                sql="select * from hnww_dayflow1 where device_id=? and dyear=? and dmonth=? and dday=?";
                List<Map<String,Object>> list_dayflow_old=jdbcTemplate.queryForList(sql,new Object[]{device_id,dyear,dmonth,dday});
                if(true)
                {
                    Map<String,Object> row_dayflow=new HashMap<>();
                    row_dayflow.put("device_id",device_id);
                    row_dayflow.put("dyear",dyear);
                    row_dayflow.put("dmonth",dmonth);
                    row_dayflow.put("dday",dday);
                    row_dayflow.put("dlog_id1",list_dlog_now.get(0).get("id"));
                    row_dayflow.put("flow1",list_dlog_now.get(0).get("flow"));
                    row_dayflow.put("ddate1",list_dlog_now.get(0).get("ddate"));
                    row_dayflow.put("dlog_id2",list_dlog_last.get(0).get("id"));
                    row_dayflow.put("flow2",list_dlog_last.get(0).get("flow"));
                    row_dayflow.put("ddate2",list_dlog_last.get(0).get("ddate"));
                    row_dayflow.put("flow",new BigDecimal(row_dayflow.get("flow1")+"").subtract(new BigDecimal(row_dayflow.get("flow2")+"")).setScale(4,RoundingMode.HALF_UP).doubleValue());
                    if(list_dayflow_old.size()>0)
                    {
                        row_dayflow.put("id",list_dayflow_old.get(0).get("id"));
                        int id1=Integer.parseInt(list_dlog_now.get(0).get("id")+"");
                        int id2=Integer.parseInt(list_dlog_last.get(0).get("id")+"");
                        int id1_old=Integer.parseInt(list_dayflow_old.get(0).get("dlog_id1")+"");
                        int id2_old=Integer.parseInt(list_dayflow_old.get(0).get("dlog_id2")+"");
                        if(id1!=id1_old||id2!=id2_old)
                        {
                            dbService.save(columns_dayflow1,"dayflow1",row_dayflow);
                        }
                    }
                    else {
                        dbService.save(columns_dayflow1,"dayflow1",row_dayflow);
                    }
                }
            }
            if(true)
            {
                //下一天
                String new_day=common.add_day(common.parse_date(dyear+"-"+(dmonth<10?"0":"")+dmonth+"-"+(dday<10?"0":"")+dday+" 00:00:00"),1);
                int dyear1= common.get_year(common.parse_date(new_day));
                int dmonth1= common.get_month(common.parse_date(new_day));
                int dday1= common.get_day(common.parse_date(new_day));
                sql="select * from hnww_dayflow1 where device_id=? and dyear=? and dmonth=? and dday=?";
                List<Map<String,Object>> list_dayflow_old=jdbcTemplate.queryForList(sql,new Object[]{device_id,dyear1,dmonth1,dday1});
                if(list_dlog_next.size()==0)
                {
                    Map<String,Object> row_dayflow=new HashMap<>();
                    row_dayflow.put("device_id",device_id);
                    row_dayflow.put("dyear",dyear1);
                    row_dayflow.put("dmonth",dmonth1);
                    row_dayflow.put("dday",dday1);
                    row_dayflow.put("dlog_id1",list_dlog_next.get(0).get("id"));
                    row_dayflow.put("flow1",list_dlog_next.get(0).get("flow"));
                    row_dayflow.put("ddate1",list_dlog_next.get(0).get("ddate"));
                    row_dayflow.put("dlog_id2",list_dlog_now.get(0).get("id"));
                    row_dayflow.put("flow2",list_dlog_now.get(0).get("flow"));
                    row_dayflow.put("ddate2",list_dlog_now.get(0).get("ddate"));
                    row_dayflow.put("flow",new BigDecimal(row_dayflow.get("flow1")+"").subtract(new BigDecimal(row_dayflow.get("flow2")+"")).setScale(4,RoundingMode.HALF_UP).doubleValue());
                    if(list_dayflow_old.size()>0)
                    {
                        row_dayflow.put("id",list_dayflow_old.get(0).get("id"));
                        int id1=Integer.parseInt(list_dlog_next.get(0).get("id")+"");
                        int id2=Integer.parseInt(list_dlog_now.get(0).get("id")+"");
                        int id1_old=Integer.parseInt(list_dayflow_old.get(0).get("dlog_id1")+"");
                        int id2_old=Integer.parseInt(list_dayflow_old.get(0).get("dlog_id2")+"");
                        if(id1!=id1_old||id2!=id2_old)
                        {
                            dbService.save(columns_dayflow1,"dayflow1",row_dayflow);
                        }
                    }
                    else {
                        dbService.save(columns_dayflow1,"dayflow1",row_dayflow);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_dayflow",device_id+":"+common.format_exception(ex));
            log.error("update_dayflow",ex);
        }
    }
    public void update_monthflow(int device_id,int dyear,int dmonth)
    {
        try {
            List<Map<String,Object>> columns_monthflow1=dbService.columns("monthflow1");
            String sql="";
            Date now=common.make_date(dyear,dmonth,1);
            Date last_month=common.add_month(now,-1);
            Date next_month=common.add_month(now,1);
            //查询当天的row_dlog
            sql="select * from hnww_dlog"+device_id+" where last_of_month=1 and deleted=0 and dyear=? and dmonth=?";
            List<Map<String,Object>> list_dlog_now=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(now),common.get_month(now)});
            List<Map<String,Object>> list_dlog_last=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(last_month),common.get_month(last_month)});
            List<Map<String,Object>> list_dlog_next=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(next_month),common.get_month(next_month)});
            if(list_dlog_now.size()==0)
            {
                throw new RuntimeException("当前月份的最新记录为空");
            }
            if(list_dlog_last.size()==1)
            {
                sql="select * from hnww_monthflow1 where device_id=? and dyear=? and dmonth=?";
                List<Map<String,Object>> list_monthflow_old=jdbcTemplate.queryForList(sql,new Object[]{device_id,dyear,dmonth});
                if(true)
                {
                    Map<String,Object> row_dayflow=new HashMap<>();
                    row_dayflow.put("device_id",device_id);
                    row_dayflow.put("dyear",dyear);
                    row_dayflow.put("dmonth",dmonth);
                    row_dayflow.put("dlog_id1",list_dlog_now.get(0).get("id"));
                    row_dayflow.put("flow1",list_dlog_now.get(0).get("flow"));
                    row_dayflow.put("ddate1",list_dlog_now.get(0).get("ddate"));
                    row_dayflow.put("dlog_id2",list_dlog_last.get(0).get("id"));
                    row_dayflow.put("flow2",list_dlog_last.get(0).get("flow"));
                    row_dayflow.put("ddate2",list_dlog_last.get(0).get("ddate"));
                    row_dayflow.put("flow",new BigDecimal(row_dayflow.get("flow1")+"").subtract(new BigDecimal(row_dayflow.get("flow2")+"")).setScale(4,RoundingMode.HALF_UP).doubleValue());
                    if(list_monthflow_old.size()>0)
                    {
                        row_dayflow.put("id",list_monthflow_old.get(0).get("id"));
                        int id1=Integer.parseInt(list_dlog_now.get(0).get("id")+"");
                        int id2=Integer.parseInt(list_dlog_last.get(0).get("id")+"");
                        int id1_old=Integer.parseInt(list_monthflow_old.get(0).get("dlog_id1")+"");
                        int id2_old=Integer.parseInt(list_monthflow_old.get(0).get("dlog_id2")+"");
                        if(id1!=id1_old||id2!=id2_old)
                        {
                            dbService.save(columns_monthflow1,"monthflow1",row_dayflow);
                        }
                    }
                    else {
                        dbService.save(columns_monthflow1,"monthflow1",row_dayflow);
                    }
                }
            }
            if(list_dlog_next.size()==1)
            {
                //下一天
                Date new_month=common.add_month(common.parse_date(dyear+"-"+(dmonth<10?"0":"")+dmonth+"-"+"1"+" 00:00:00"),1);
                int dyear1= common.get_year(new_month);
                int dmonth1= common.get_month(new_month);
                sql="select * from hnww_monthflow1 where device_id=? and dyear=? and dmonth=?";
                List<Map<String,Object>> list_monthflow_old=jdbcTemplate.queryForList(sql,new Object[]{device_id,dyear1,dmonth1});
                if(true)
                {
                    Map<String,Object> row_dayflow=new HashMap<>();
                    row_dayflow.put("device_id",device_id);
                    row_dayflow.put("dyear",dyear1);
                    row_dayflow.put("dmonth",dmonth1);
                    row_dayflow.put("dlog_id1",list_dlog_next.get(0).get("id"));
                    row_dayflow.put("flow1",list_dlog_next.get(0).get("flow"));
                    row_dayflow.put("ddate1",list_dlog_next.get(0).get("ddate"));
                    row_dayflow.put("dlog_id2",list_dlog_now.get(0).get("id"));
                    row_dayflow.put("flow2",list_dlog_now.get(0).get("flow"));
                    row_dayflow.put("ddate2",list_dlog_now.get(0).get("ddate"));
                    row_dayflow.put("flow",new BigDecimal(row_dayflow.get("flow1")+"").subtract(new BigDecimal(row_dayflow.get("flow2")+"")).setScale(4,RoundingMode.HALF_UP).doubleValue());
                    if(list_monthflow_old.size()>0)
                    {
                        row_dayflow.put("id",list_monthflow_old.get(0).get("id"));
                        int id1=Integer.parseInt(list_dlog_next.get(0).get("id")+"");
                        int id2=Integer.parseInt(list_dlog_now.get(0).get("id")+"");
                        int id1_old=Integer.parseInt(list_monthflow_old.get(0).get("dlog_id1")+"");
                        int id2_old=Integer.parseInt(list_monthflow_old.get(0).get("dlog_id2")+"");
                        if(id1!=id1_old||id2!=id2_old)
                        {
                            dbService.save(columns_monthflow1,"monthflow1",row_dayflow);
                        }
                    }
                    else {
                        dbService.save(columns_monthflow1,"monthflow1",row_dayflow);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_monthflow",device_id+":"+common.format_exception(ex));
            log.error("update_dayflow",ex);
        }
    }
    public void update_quarterflow(int device_id,int dyear,int dquarter)
    {
        try {
            List<Map<String,Object>> columns_quarterflow=dbService.columns("quarterflow");
            String sql="";
            int smonth=(dquarter-1)*3+1;
            int emonth=(dquarter)*3;
            Date now=common.make_date(dyear,1,1);
            Date last_month=common.add_month(now,-1);
            Date next_month=common.add_month(now,1);
            //查询当天的row_dlog
            sql="select * from hnww_dlog"+device_id+" where last_of_month=1 and deleted=0 and dyear=? and dmonth>=? and dmonth<=? order by ddate asc limit 0,1";
            List<Map<String,Object>> list_dlog_start=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(now),smonth,emonth});
            sql="select * from hnww_dlog"+device_id+" where last_of_month=1 and deleted=0 and dyear=? and dmonth>=? and dmonth<=? order by ddate desc limit 0,1";
            List<Map<String,Object>> list_dlog_end=jdbcTemplate.queryForList(sql,new Object[]{common.get_year(now),smonth,emonth});
            if(list_dlog_start.size()==1&&list_dlog_end.size()==1)
            {
                sql="select * from hnww_quarterflow where device_id=? and dyear=? and dquarter=?";
                List<Map<String,Object>> list_quarterflow_old=jdbcTemplate.queryForList(sql,new Object[]{device_id,dyear,dquarter});
                if(true)
                {
                    Map<String,Object> row_dayflow=new HashMap<>();
                    row_dayflow.put("device_id",device_id);
                    row_dayflow.put("dyear",dyear);
                    row_dayflow.put("dquarter",dquarter);
                    row_dayflow.put("dlog_id1",list_dlog_start.get(0).get("id"));
                    row_dayflow.put("flow1",list_dlog_start.get(0).get("flow"));
                    row_dayflow.put("ddate1",list_dlog_start.get(0).get("ddate"));
                    row_dayflow.put("dlog_id2",list_dlog_end.get(0).get("id"));
                    row_dayflow.put("flow2",list_dlog_end.get(0).get("flow"));
                    row_dayflow.put("ddate2",list_dlog_end.get(0).get("ddate"));
                    row_dayflow.put("flow",new BigDecimal(row_dayflow.get("flow1")+"").subtract(new BigDecimal(row_dayflow.get("flow2")+"")).setScale(4,RoundingMode.HALF_UP).doubleValue());
                    if(list_quarterflow_old.size()>0)
                    {
                        row_dayflow.put("id",list_quarterflow_old.get(0).get("id"));
                        dbService.save(columns_quarterflow,"quarterflow",row_dayflow);
                    }
                    else {
                        dbService.save(columns_quarterflow,"quarterflow",row_dayflow);
                    }
                    fileUtil.add_msg("update_quarterflow",JSONObject.toJSONString(row_dayflow));
                }
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_quarterflow",device_id+":"+common.format_exception(ex));
            log.error("update_quarterflow",ex);
        }
    }
    public void update_yearflow(int device_id)
    {
        try {
            List<Map<String,Object>> columns_yearflow=dbService.columns("yearflow");
            String sql="";
            sql="select distinct(dyear) as dyear from hnww_dlog"+device_id+" where deleted=0";
            List<Map<String,Object>> list_dyear=jdbcTemplate.queryForList(sql);
            for(int i=0;i<list_dyear.size();i++)
            {
                int dyear=Integer.parseInt(list_dyear.get(i).get("dyear")+"");
                //查询当天的row_dlog
                sql="select * from hnww_dlog"+device_id+" where deleted=0 and dyear=? order by ddate asc limit 0,1";
                List<Map<String,Object>> list_dlog_start=jdbcTemplate.queryForList(sql,new Object[]{dyear});
                sql="select * from hnww_dlog"+device_id+" where deleted=0 and dyear=? order by ddate desc limit 0,1";
                List<Map<String,Object>> list_dlog_end=jdbcTemplate.queryForList(sql,new Object[]{dyear});
                if(list_dlog_start.size()==1&&list_dlog_end.size()==1)
                {
                    sql="select * from hnww_yearflow where device_id=? and dyear=?";
                    List<Map<String,Object>> list_quarterflow_old=jdbcTemplate.queryForList(sql,new Object[]{device_id,dyear});
                    if(true)
                    {
                        Map<String,Object> row_dayflow=new HashMap<>();
                        row_dayflow.put("device_id",device_id);
                        row_dayflow.put("dyear",dyear);
                        row_dayflow.put("dlog_id1",list_dlog_start.get(0).get("id"));
                        row_dayflow.put("flow1",list_dlog_start.get(0).get("flow"));
                        row_dayflow.put("ddate1",list_dlog_start.get(0).get("ddate"));
                        row_dayflow.put("dlog_id2",list_dlog_end.get(0).get("id"));
                        row_dayflow.put("flow2",list_dlog_end.get(0).get("flow"));
                        row_dayflow.put("ddate2",list_dlog_end.get(0).get("ddate"));
                        row_dayflow.put("flow",new BigDecimal(row_dayflow.get("flow1")+"").subtract(new BigDecimal(row_dayflow.get("flow2")+"")).setScale(4,RoundingMode.HALF_UP).doubleValue());
                        if(list_quarterflow_old.size()>0)
                        {
                            row_dayflow.put("id",list_quarterflow_old.get(0).get("id"));
                            dbService.save(columns_yearflow,"yearflow",row_dayflow);
                        }
                        else {
                            dbService.save(columns_yearflow,"yearflow",row_dayflow);
                        }
                    }
                }
                for(int j=1;j<=4;j++)
                {
                    update_quarterflow(device_id,dyear,j);
                }
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_quarterflow",device_id+":"+common.format_exception(ex));
            log.error("update_yearflow",ex);
        }
    }
    //定时更新dlogcheck
    @Scheduled(fixedDelay = 10000)
    public void do_dsend()
    {
        try {
            List<Map<String,Object>> column_dsendtask=dbService.columns("dsendtask");
            String sql="";
            sql="select * from hnww_dsendtask where deleted=0 and ftime=0 order by id desc limit 0,1000";
            List<Map<String,Object>> list_dsendtask=jdbcTemplate.queryForList(sql);
            list_dsendtask=dbService.get_dsendtask_detail(list_dsendtask);
            JSONArray rows_dsendtask=JSONArray.parseArray(JSONArray.toJSONString(list_dsendtask));
            for(int i=0;i<rows_dsendtask.size();i++)
            {
                JSONObject row_dsendtask=rows_dsendtask.getJSONObject(i);
                to_do_dsend(row_dsendtask);
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("do_dsend",common.format_exception(ex));
        }
    }
    //定时更新dlogcheck
    public void to_do_dsend(JSONObject row_dsendtask)
    {
        try {
            fileUtil.add_msg("do_dsend",row_dsendtask.toJSONString());
            List<Map<String,Object>> column_dsendtask=dbService.columns("dsendtask");
            String ddate=jsonUtil.get_string_by_path(row_dsendtask.toJSONString(),"$.row_dlog.ddate");
            String now=common.format_date(new Date());
            if(ddate!=null&&ddate.substring(0,7).equals(now.substring(0,7)))
            {
                String msg=dsendUtil.get_msg(row_dsendtask);
                if(!common.is_empty(msg))
                {
                    socketUtil.send(msg);
                }
                //更新
                Map<String,Object> row=new HashMap<>();
                row.put("id",jsonUtil.get_int_by_path(row_dsendtask.toJSONString(),"$.id"));
                row.put("msg",msg);
                row.put("ip",ip);
                row.put("port",port);
                row.put("ftime",(int)(new Date().getTime()/1000));
                dbService.save(column_dsendtask,"dsendtask",row);
                Thread.sleep(1000);
            }
            else
            {
                Map<String,Object> row=new HashMap<>();
                row.put("id",jsonUtil.get_int_by_path(row_dsendtask.toJSONString(),"$.id"));
                row.put("msg",now+":"+ddate);
                row.put("ip",ip);
                row.put("port",port);
                row.put("ftime",(int)(new Date().getTime()/1000));
                dbService.save(column_dsendtask,"dsendtask",row);
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("do_dsend",common.format_exception(ex));
        }
    }

    //定时更新dlogcheck
//    @Scheduled(fixedDelay = 3600000)
    public void ini_msg_path()
    {
        try {
            String path="/www/wwwroot/hnweb/msg";
            Files.walkFileTree(Paths.get(path),new SimpleFileVisitor<Path>(){
                @Override
                public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {
                    File f=new File(file.toString());
                    if(f.getParent().equals(path))
                    {
                        Date d=new Date(f.lastModified());
                        String new_path=f.getParent()+"/"+common.format_date(d).substring(0,10)+"/"+f.getName();
                        fileUtil.copy_file(file.toString(),new_path);
                        fileUtil.add_msg("ini_msg_path",file.toString());
                        log.info("ini_msg_path:"+file.toString());
                    }
                    return FileVisitResult.CONTINUE;
                }
            });
        }
        catch (Exception ex)
        {;
            log.info("ini_msg_path:"+common.format_exception(ex));
            fileUtil.add_msg("ini_msg_path", common.format_exception(ex));
        }
    }
    //生成费用清单
    @Scheduled(fixedDelay = 60000)
    public void make_fee()
    {
        //
        try {
            List<Map<String,Object>> columns_fee=dbService.columns("fee");
            List<Map<String,Object>> columns_feelist=dbService.columns("feelist");
            String sql="select * from hnww_site where deleted=0";
            List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql);
            if(rows_site!=null&&rows_site.size()>0)
            {
                rows_site=dbService.get_site_detail(rows_site);
                log.info("make_fee:站点数量="+rows_site.size());
                for(int i=0;i<rows_site.size();i++)
                {
                    JSONObject row_site=JSONObject.parseObject(JSONObject.toJSONString(rows_site.get(i)));
                    if(row_site!=null)
                    {
                        //开始日期
                        int site_id=jsonUtil.get_int_by_path(row_site.toJSONString(),"$.id");
                        String sdate=jsonUtil.get_string_by_path(row_site.toJSONString(),"$.sdate");
                        double price=jsonUtil.get_double_by_path(row_site.toJSONString(),"$.price");
                        log.info("make_fee:站点信息="+site_id+":"+sdate+":"+price);
                        //获取plan
                        sql="select * from hnww_plan where state=1 and site_id="+site_id;
                        JSONArray rows_plan=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
                        log.info("make_fee:站点计划="+(rows_plan==null?"null":rows_plan.size()));
                        if(!common.is_empty(sdate)&&rows_plan!=null)
                        {
                            //循环生成fee的年月
                            List<Date> list_date=get_dates(sdate);
                            log.info("make_fee:年月数量="+(list_date==null?"null":list_date.size()));
                            //是否有设备
                            JSONArray rows_device=jsonUtil.get_jsonarray_by_path(row_site.toJSONString(),"$.rows_device");
                            log.info("make_fee:站点设备="+(rows_device==null?"null":rows_device.size()));
                            if(rows_device!=null&&rows_device.size()>0&&list_date.size()>0)
                            {
                                for(int k=0;k<list_date.size();k++)
                                {
                                    Date now_date=list_date.get(k);
                                    int year= common.get_year(now_date);
                                    int month= common.get_month(now_date);
                                    log.info("make_fee:当前年月="+year+"-"+month);
                                    //查询fee是否存在
                                    sql="select * from hnww_fee where site_id="+site_id+" and year="+year+" and month="+month+" and deleted=0";
                                    List<Map<String,Object>> rows_fee=jdbcTemplate.queryForList(sql);
                                    log.info("make_fee:已存在fee记录数量="+(rows_fee==null?"null":rows_fee.size()));
                                    if(rows_fee!=null&&rows_fee.size()==0)
                                    {
                                        Map<String,Object> row_fee=new HashMap<>();
                                        List<Map<String,Object>> rows_feelist=new ArrayList<>();
                                        row_fee.put("site_id",site_id);
                                        row_fee.put("year",year);
                                        row_fee.put("month",month);
                                        //查询
                                        int index=-1;
                                        for(int a=0;a<rows_plan.size();a++)
                                        {
                                            JSONObject row_plan=rows_plan.getJSONObject(a);
                                            if(row_plan!=null)
                                            {
                                                int item_year=jsonUtil.get_int_by_path(row_plan.toJSONString(),"$.year");
                                                if(item_year==year)
                                                {
                                                    index=a;
                                                    a=rows_plan.size();
                                                }
                                            }
                                        }
                                        BigDecimal plan=new BigDecimal(0);
                                        if(index>=0)
                                        {
                                            plan=new BigDecimal(jsonUtil.get_double_by_path(rows_plan.getJSONObject(index).toJSONString(),"$.plan"+month));
                                            if(plan.doubleValue()<=0)
                                            {
                                                plan=new BigDecimal(0);
                                            }
                                        }
                                        log.info("make_fee:当前plan="+(plan));
                                        row_fee.put("plan",plan);
                                        BigDecimal total_flow=new BigDecimal(0);
                                        BigDecimal fee=new BigDecimal(0);
                                        for(int j=0;j<rows_device.size();j++)
                                        {
                                            JSONObject row_device=rows_device.getJSONObject(j);
                                            int device_id=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.id");
                                            //需要的数据
                                            Map<String,Object> row_feelist=new HashMap<>();
                                            row_feelist.put("site_id",site_id);
                                            row_feelist.put("device_id",device_id);
                                            row_feelist.put("year",year);
                                            row_feelist.put("month",month);
                                            int sdlog_id=0;
                                            BigDecimal sflow=new BigDecimal(0);
                                            int edlog_id=0;
                                            BigDecimal eflow=new BigDecimal(0);
                                            sql="select * from hnww_monthflow where device_id="+device_id+" and dyear="+year+" and dmonth="+month;
                                            JSONArray rows_monthflow=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
                                            if(rows_monthflow!=null&&rows_monthflow.size()==1)
                                            {
                                                sdlog_id=jsonUtil.get_int_by_path(rows_monthflow.getJSONObject(0).toJSONString(),"$.id2");
                                                sflow=new BigDecimal(jsonUtil.get_double_by_path(rows_monthflow.getJSONObject(0).toJSONString(),"$.flow2"));
                                                edlog_id=jsonUtil.get_int_by_path(rows_monthflow.getJSONObject(0).toJSONString(),"$.id");
                                                eflow=new BigDecimal(jsonUtil.get_double_by_path(rows_monthflow.getJSONObject(0).toJSONString(),"$.flow"));
                                            }
                                            sflow=sflow.setScale(2,BigDecimal.ROUND_HALF_UP);
                                            eflow=eflow.setScale(2,BigDecimal.ROUND_HALF_UP);
                                            row_feelist.put("sdlog_id",sdlog_id);
                                            row_feelist.put("sflow",sflow.doubleValue());
                                            row_feelist.put("edlog_id",edlog_id);
                                            row_feelist.put("eflow",eflow.doubleValue());
                                            double fflow=eflow.doubleValue()-sflow.doubleValue();
                                            if(fflow<0)
                                            {
                                                fflow=0;
                                            }
                                            row_feelist.put("fflow",fflow);
                                            row_feelist.put("fnote","");
                                            row_feelist.put("fdate",common.format_date(new Date()));
                                            row_feelist.put("deleted",0);
                                            total_flow=total_flow.add(new BigDecimal(fflow));
                                            rows_feelist.add(row_feelist);
                                        }
                                        row_fee.put("flow",total_flow.setScale(2, RoundingMode.HALF_UP).doubleValue());
                                        row_fee.put("price",price);
                                        row_fee.put("fnote","");
                                        row_fee.put("modify_user_id",0);
                                        row_fee.put("check_user_id",0);
                                        row_fee.put("check_time",0);
                                        row_fee.put("state",0);
                                        row_fee.put("fdate",common.format_date(new Date()));
                                        row_fee.put("deleted",0);
                                        //计算fee
                                        row_fee.put("fee",get_fee(total_flow.doubleValue(),price,plan.doubleValue(),jsonUtil.get_jsonobject_by_path(row_site.toJSONString(),"$.row_feestep")));
                                        log.info("make_fee:row_fee="+JSONObject.toJSONString(row_fee));
                                        log.info("make_fee:rows_feelist="+JSONArray.toJSONString(rows_feelist));
                                        //开始添加
                                        int res_fupdate=dbService.save(columns_fee,"fee",row_fee);
                                        if(res_fupdate>0)
                                        {
                                            for(int j=0;j<rows_feelist.size();j++)
                                            {
                                                Map<String,Object> row_feelist=rows_feelist.get(j);
                                                row_feelist.put("fee_id",res_fupdate);
                                                dbService.save(columns_feelist,"feelist",row_feelist);
                                            }
                                        }
                                        else {
                                            log.info("添加fee失败");
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("make_fee",common.format_exception(ex));
        }
    }
    public double get_fee(double flow,double price,double plan,JSONObject row_feestep)
    {
        BigDecimal res=new BigDecimal(0);
        res=new BigDecimal(price).multiply(new BigDecimal(flow)).setScale(2,RoundingMode.HALF_UP);
        if(row_feestep!=null&&plan>0&&flow>plan)
        {
            String percents=jsonUtil.get_string_by_path(row_feestep.toJSONString(),"$.percents");
            String prices=jsonUtil.get_string_by_path(row_feestep.toJSONString(),"$.prices");
            String[] list_percents=percents.split(",");
            String[] list_price=percents.split(",");
            boolean check=true;
            for(int i=0;i<list_percents.length;i++)
            {
                if(!common.is_number(list_percents[i]))
                {
                    check=false;
                    i=list_percents.length;
                }
            }
            if(check)
            {
                for(int i=0;i<list_price.length;i++)
                {
                    if(!common.is_number(list_price[i]))
                    {
                        check=false;
                        i=list_price.length;
                    }
                }
            }
            if(check)
            {
                res=new BigDecimal(price).multiply(new BigDecimal(plan)).setScale(2,RoundingMode.HALF_UP);
                double now_rate=new BigDecimal(flow/plan).setScale(2,RoundingMode.HALF_UP).doubleValue();
                for(int i=0;i<=list_percents.length;i++)
                {
                    double start_percent=0;
                    if(i>0)
                    {
                        start_percent=Double.parseDouble(list_percents[i-1]);
                    }
                    if(i<list_percents.length)
                    {
                        double end_percent=Double.parseDouble(list_percents[i]);
                        double start=((double) 100+start_percent)/100;
                        double end=((double) 100+end_percent)/100;
                        if(now_rate>start)
                        {
                            if(now_rate>end)
                            {
                                res=res.add(new BigDecimal(price*(end-start)).setScale(2,RoundingMode.HALF_UP));
                            }
                            else {
                                res=res.add(new BigDecimal(price*(now_rate-start)).setScale(2,RoundingMode.HALF_UP));
                            }
                        }
                    }
                    else
                    {
                        double start=((double) 100+start_percent)/100;
                        if(now_rate>start)
                        {
                            res=res.add(new BigDecimal(price*(now_rate-start)).setScale(2,RoundingMode.HALF_UP));
                        }
                    }
                }
            }
        }
        return res.doubleValue();
    }
    public List<Date> get_dates(String sdate)
    {
        List<Date> res=new ArrayList<>();
        if(sdate.length()!=7)
        {
            return res;
        }
        Date d=common.parse_date(sdate+"-01 00:00:00");
        if(d==null)
        {
            return res;
        }
        Date now=common.parse_date(common.format_date(new Date()).substring(0,7)+"-01 00:00:00");
        if(now==null)
        {
            return res;
        }
        while (d.getTime()<now.getTime())
        {
            res.add(d);
            d=common.add_month(d,1);
        }
        return res;
    }

    @Scheduled(cron = "3 * * * * ?")
    public void update_interval()
    {
        try {
            //查询没有sync的device
            String sql="select t1.* from hnww_device as t1 left join hnww_syncinterval as t2 on t1.id=t2.device_id where t1.deleted=0 and t1.state=1 and t2.id is null order by t1.id asc";
            List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
            Map<String,Object> row_device=null;
            if(rows_device!=null&&rows_device.size()>0)
            {
                row_device=rows_device.get(0);
            }
            if(row_device==null)
            {
                sql="select t1.* from hnww_device as t1 left join hnww_syncinterval as t2 on t1.id=t2.device_id where t1.deleted=0 and t1.state=1 and t2.id is not null order by t2.fdate asc";
                rows_device=jdbcTemplate.queryForList(sql);
                if(rows_device!=null&&rows_device.size()>0)
                {
                    row_device=rows_device.get(0);
                }
            }
            if(row_device!=null)
            {
                //读取时间间隔
                String channel_id=row_device.get("channel_id").toString();
                rabbitUtil.respon(channel_id,"0503120100029137");
                redisUtil.set(channel_id,"05031201",expire);
                sql="select * from hnww_syncinterval where device_id="+row_device.get("id").toString();
                List<Map<String,Object>> rows_synctime=jdbcTemplate.queryForList(sql);
                if(rows_synctime==null||rows_synctime.size()==0)
                {
                    sql="insert into hnww_syncinterval (device_id,fdate,deleted) values (?,?,0)";
                    Object[] ps1={row_device.get("id").toString(),common.format_date(new Date())};
                    jdbcTemplate.update(sql,ps1);
                }
                else {
                    sql="update hnww_syncinterval set fdate=? where id="+rows_synctime.get(0).get("id").toString();
                    Object[] ps2={common.format_date(new Date())};
                    jdbcTemplate.update(sql,ps2);
                }
            }
            else {
                throw new Exception("没有需要更新的设备");
            }
        }
        catch (Exception ex)
        {
            fileUtil.add_msg("update_interval",common.format_date(new Date())+common.format_exception(ex));
        }
    }

    @Scheduled(cron = "0 0 * * * ?")
    public void get_tianqi() {
        String res=restTemplate.getForObject("https://api.map.baidu.com/weather/v1/?district_id=460100&data_type=all&ak=jeGEVlSManTYSKzUr7k4taE4QxGwh4G7",String.class);
        try {
            JSONObject jsonObject= JSON.parseObject(res);
            int code=jsonObject.getInteger("status");
            if(code==0)
            {
                JSONArray list_predict=jsonUtil.get_json_array(jsonObject,"result,forecasts");
                if(list_predict!=null)
                {
                    for(int i=0;i<list_predict.size();i++)
                    {
                        JSONObject predict=list_predict.getJSONObject(i);
                        String date=jsonUtil.get_json_string(predict,"date");
                        String day_temperature=jsonUtil.get_json_string(predict,"high");
                        String day_info=jsonUtil.get_json_string(predict,"text_day");
                        String night_temperature=jsonUtil.get_json_string(predict,"low");
                        String night_info=jsonUtil.get_json_string(predict,"text_night");
                        //查询是否存在
                        Map<String,String[]> condition=new HashMap<>();
                        condition.put("fdate",new String[]{"=",date});
                        condition.put("deleted",new String[]{"=","0"});
                        List<Map<String,Object>> columns_weather=dbService.columns("weather");
                        List<Map<String,Object>> list_weather=dbService.query(columns_weather,"weather",condition,"","");
                        Map<String,Object> row=new HashMap<>();
                        if(list_weather!=null&&list_weather.size()>0)
                        {
                            Map<String,Object> row_weather=list_weather.get(0);
                            row.put("id",row_weather.get("id"));
                            row.put("fdate",row_weather.get("fdate"));
                            row.put("deleted",0);
                            row.put("city","海口");
                            row.put("code","59758");
                            row.put("day_info",day_info);
                            row.put("day_temperature",day_temperature);
                            row.put("night_info",night_info);
                            row.put("night_temperature",night_temperature);
                        }
                        else {
                            row.put("fdate",date);
                            row.put("deleted",0);
                            row.put("city","海口");
                            row.put("code","59758");
                            row.put("day_info",day_info);
                            row.put("day_temperature",day_temperature);
                            row.put("night_info",night_info);
                            row.put("night_temperature",night_temperature);
                        }
                        log.info(row.toString());
                        dbService.save(columns_weather,"weather",row);
                    }
                }
                else {
                    log.info("天气接口:"+"获取predict失败");
                }
            }
            else {
                log.info(jsonObject.getString("msg"));
            }
        }
        catch (Exception ex)
        {
            log.info(ex.toString());
        }
    }

    @Scheduled(fixedDelay = 5000)
    public void check_dlog_table() {
        String sql="";
        try {
            sql="select * from "+dbService.prefix+"_device where table_name='' limit 0,10 ";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql);
            for(int i=0;i<list_device.size();i++) {
                int id = Integer.parseInt(list_device.get(i).get("id") + "");
                String table_name = "hnww_dlog" + id;
                sql = "SELECT table_name FROM information_schema.tables where table_name=?";
                List<Map<String, Object>> list_info = jdbcTemplate.queryForList(sql, new Object[]{table_name});
                if (list_info.size() == 0)
                {
                    sql="CREATE TABLE \"hnww\".\""+table_name+"\" (LIKE \"hnww\".\"hnww_dlog\" INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING COMMENTS);";
                    jdbcTemplate.update(sql);
                }
                sql="update hnww_device set table_name=? where id=?";
                jdbcTemplate.update(sql,new Object[]{table_name,id});
                fileUtil.add_msg("check_dlog_table","done:"+id);
            }
        }
        catch (Exception ex)
        {
            log.info(ex.toString());
            fileUtil.add_msg("check_dlog_table",common.format_exception(ex));
        }
    }
    @Scheduled(fixedDelay = 5000)
    public void do_move_dlog() {
        String sql="";
        try {
            sql="select * from "+dbService.prefix+"_device where deleted=0 order by id asc";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql);
            for(int i=0;i<list_device.size();i++) {
                int id = Integer.parseInt(list_device.get(i).get("id") + "");
                String table_name = "hnww_dlog" + id;
                sql = "SELECT table_name FROM information_schema.tables where table_name=?";
                List<Map<String, Object>> list_info = jdbcTemplate.queryForList(sql, new Object[]{table_name});
                if (list_info.size()>0)
                {
                    int max_id=0;
                    sql="select max(id) as max_id from "+dbService.prefix+"_dlog"+id;
                    List<Map<String,Object>> list_dlog1=jdbcTemplate.queryForList(sql);
                    if(list_dlog1.size()>0&&list_dlog1.get(0).get("max_id")!=null)
                    {
                        max_id=Integer.parseInt(list_dlog1.get(0).get("max_id")+"");
                    }
                    sql="INSERT INTO hnww_dlog"+id+" SELECT * FROM hnww_dlog  WHERE device_id ="+id+" and id>"+max_id+" order by id asc";
                    jdbcTemplate.update(sql);
//                    sql="delete from hnww_dlog where device_id=? and id<=?";
//                    jdbcTemplate.update(sql,new Object[]{id,max_id});
                    fileUtil.add_msg("do_move_dlog",i+"/"+list_device.size()+":"+id);
                }
                else {
                    fileUtil.add_msg("do_move_dlog",i+"/"+list_device.size()+":"+id+":没有分表");
                }
            }
        }
        catch (Exception ex)
        {
            log.info(ex.toString());
            fileUtil.add_msg("do_move_dlog",common.format_exception(ex));
        }
    }

    //定时更新dlogcheck
    @Scheduled(fixedDelay = 1800000)
    public void do_alarm()
    {
        String sql="";
        Date now=new Date();
        try {
            List<Map<String,Object>> rows_column=dbService.columns("alarm");
            sql="select * from "+dbService.prefix+"_device where deleted=0 order by id asc";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql);
            sql="select * from "+dbService.prefix+"_alarmconfig where deleted=0 order by id asc";
            List<Map<String,Object>> list_alarmconfig=jdbcTemplate.queryForList(sql);
            for(int j=0;j<list_device.size();j++)
            {
                JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(j)));
                int device_id=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.id");
                int dtype=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.dtype");
                int detail_id=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.detail_id");
                //查询最新一条记录
                sql="select * from hnww_dlog"+device_id+" order by ddate desc limit 0,1";
                List<Map<String,Object>> rows_dlog=jdbcTemplate.queryForList(sql);
                JSONObject row_dlog=null;
                if(rows_dlog.size()==1)
                {
                    row_dlog=JSONObject.parseObject(JSONObject.toJSONString(rows_dlog.get(0)));
                }
                //查询当月的用水计划
                JSONObject row_plan=null;
                JSONObject row_site=null;
                double limit=-1;
                if(dtype==0)
                {
                    sql="select * from hnww_plan where deleted=0 and site_id=? and state=1 and `year`=?";
                    List<Map<String,Object>> rows_plan=jdbcTemplate.queryForList(sql,new Object[]{detail_id,common.get_year(now)});
                    if(rows_plan.size()==1)
                    {
                        row_plan=JSONObject.parseObject(JSONObject.toJSONString(rows_plan.get(0)));
                    }
                    //查询对应的用水站点的用水许可
                    sql="select * from hnww_site where deleted=0 and id=?";
                    List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql,new Object[]{detail_id});
                    if(rows_site.size()==1)
                    {
                        row_site=JSONObject.parseObject(JSONObject.toJSONString(rows_site.get(0)));
                        if(common.is_number(jsonUtil.get_string_by_path(row_site.toJSONString(),"$.field_132")))
                        {
                            limit=Double.parseDouble(jsonUtil.get_string_by_path(row_site.toJSONString(),"$.field_132"));
                        }
                    }
                }
                //查询日用水量
                String now_date=common.format_date(now);
                JSONObject row_dayflow=null;
                double dayflow=Double.MIN_VALUE;
                sql="select * from hnww_dayflow where device_id=? and ddate1 like '"+now_date.substring(0,10)+"%'";
                List<Map<String,Object>> rows_dayflow=jdbcTemplate.queryForList(sql,new Object[]{device_id});
                if(rows_dayflow.size()==1)
                {
                    row_dayflow=JSONObject.parseObject(JSONObject.toJSONString(rows_dayflow.get(0)));
                    dayflow=jsonUtil.get_double_by_path(row_dayflow.toJSONString(),"$.flow");
                }
                //查询月用水量
                JSONObject row_monthflow=null;
                double monthflow=Double.MIN_VALUE;
                sql="select * from hnww_monthflow where device_id=? and ddate1 like '"+now_date.substring(0,7)+"%'";
                List<Map<String,Object>> rows_monthflow=jdbcTemplate.queryForList(sql,new Object[]{device_id});
                if(rows_monthflow.size()==1)
                {
                    row_monthflow=JSONObject.parseObject(JSONObject.toJSONString(rows_monthflow.get(0)));
                    monthflow=jsonUtil.get_double_by_path(row_monthflow.toJSONString(),"$.flow");
                }
                //查询年用水量
                JSONObject row_yearflow=null;
                double yearflow=Double.MIN_VALUE;
                sql="select * from hnww_yearflow where device_id=? and ddate1 like '"+now_date.substring(0,4)+"%'";
                List<Map<String,Object>> rows_yearflow=jdbcTemplate.queryForList(sql,new Object[]{device_id});
                if(rows_yearflow.size()==1)
                {
                    row_yearflow=JSONObject.parseObject(JSONObject.toJSONString(rows_yearflow.get(0)));
                    yearflow=jsonUtil.get_double_by_path(row_yearflow.toJSONString(),"$.flow");
                }
                for(int i=0;i<list_alarmconfig.size();i++) {
                    JSONObject row_alarmconfig=JSONObject.parseObject(JSONObject.toJSONString(list_alarmconfig.get(i)));
                    int id=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.id");
                    int type=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.type");
                    int for_site=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.for_site");
                    int for_resource=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.for_resource");
                    int is_contain=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.is_contain");
                    String device_ids=jsonUtil.get_string_by_path(row_alarmconfig.toJSONString(),"$.device_ids");
                    String field=jsonUtil.get_string_by_path(row_alarmconfig.toJSONString(),"$.field");
                    double sval=jsonUtil.get_double_by_path(row_alarmconfig.toJSONString(),"$.sval");
                    double eval=jsonUtil.get_double_by_path(row_alarmconfig.toJSONString(),"$.eval");
                    int enable_sval=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.enable_sval");
                    int enable_eval=jsonUtil.get_int_by_path(row_alarmconfig.toJSONString(),"$.enable_eval");
                    //判断是否符合告警设置范围
                    boolean in=false;
                    if(dtype==0&&for_site==1)
                    {
                        in=true;
                    }
                    if(dtype==1&&for_resource==1)
                    {
                        in=true;
                    }
                    String[] list_device_id=device_ids.split(",");
                    int index=-1;
                    if(!common.is_empty(device_ids))
                    {
                        for(int k=0;k<list_device_id.length;k++)
                        {
                            if(Integer.parseInt(list_device_id[k])==device_id)
                            {
                                index=k;
                                k=list_device_id.length;
                            }
                        }
                    }
                    if(index==-1&&is_contain==0)
                    {
                        in=true;
                    }
                    if(index>=0&&is_contain==1)
                    {
                        in=true;
                    }
                    if(in)
                    {
                        //查询是否存在告警
                        sql="select * from hnww_alarm where alarmconfig_id=? and device_id=? and state=0 and deleted=0";
                        List<Map<String,Object>> rows_alarm=jdbcTemplate.queryForList(sql,new Object[]{id,device_id});
                        if(type==0&&enable_sval==1&&sval>0)
                        {
                            boolean checked=true;
                            if(row_dlog!=null)
                            {
                                Date ddate=common.parse_date(jsonUtil.get_string_by_path(row_dlog.toJSONString(),"$.ddate"));
                                if((now.getTime()-ddate.getTime())/(60*1000)<sval)
                                {
                                    checked=false;
                                }
                            }
                            if(checked)
                            {
                                //如果不存在就新增
                                if(rows_alarm.size()==0)
                                {
                                    Map<String,Object> row=new HashMap<>();
                                    row.put("alarmconfig_id",id);
                                    row.put("device_id",device_id);
                                    row.put("type",type);
                                    row.put("field","");
                                    row.put("ftime",(new Date().getTime()));
                                    row.put("etime",0);
                                    row.put("fval",0);
                                    row.put("state",0);
                                    row.put("deleted",0);
                                    dbService.save(rows_column,"alarm",row);
                                }
                            }
                            else {
                                //如果存在就结束
                                if(rows_alarm.size()>0)
                                {
                                    int alarm_id=Integer.parseInt(rows_alarm.get(0).get("id")+"");
                                    Map<String,Object> row=new HashMap<>();
                                    row.put("id",alarm_id);
                                    row.put("eval",0);
                                    row.put("etime",now.getTime());
                                    row.put("edate",common.format_date(now));
                                    row.put("state",1);
                                    row.put("deleted",0);
                                    dbService.save(rows_column,"alarm",row);
                                }
                            }
                        }
                        else if(type==1&&row_dlog!=null)
                        {
                            boolean checked=false;
                            double val=jsonUtil.get_double_by_path(row_dlog.toJSONString(),"$."+field);
                            if(val!=-99)
                            {
                                if(field.equals("flowrate"))
                                {
                                    val=val/1000;
                                }
                                if((val>=sval&&enable_sval==1)&&(val<eval&&enable_eval==1))
                                {
                                    checked=true;
                                }
                            }
                            if(checked)
                            {
                                //如果不存在就新增
                                if(rows_alarm.size()==0)
                                {
                                    Map<String,Object> row=new HashMap<>();
                                    row.put("alarmconfig_id",id);
                                    row.put("device_id",device_id);
                                    row.put("type",type);
                                    row.put("field",field);
                                    row.put("ftime",(new Date().getTime()));
                                    row.put("etime",0);
                                    row.put("fval",val);
                                    row.put("state",0);
                                    row.put("deleted",0);
                                    dbService.save(rows_column,"alarm",row);
                                }
                            }
                            else {
                                //如果存在就结束
                                if(rows_alarm.size()>0)
                                {
                                    int alarm_id=Integer.parseInt(rows_alarm.get(0).get("id")+"");
                                    Map<String,Object> row=new HashMap<>();
                                    row.put("id",alarm_id);
                                    row.put("eval",val);
                                    row.put("etime",now.getTime());
                                    row.put("edate",common.format_date(now));
                                    row.put("state",1);
                                    row.put("deleted",0);
                                    dbService.save(rows_column,"alarm",row);
                                }
                            }
                        }
                        else if(type==2&&row_dlog!=null)
                        {
                            boolean checked=false;
                            double val=Double.MIN_VALUE;
                            if(field.equals("dayflow")&&dayflow!=Double.MIN_VALUE)
                            {
                                val=dayflow;
                                if((val>=sval&&enable_sval==1)&&(val<eval&&enable_eval==1))
                                {
                                    checked=true;
                                }
                            }
                            else if(field.equals("monthflow")&&monthflow!=Double.MIN_VALUE)
                            {
                                val=monthflow;
                                if((val>=sval&&enable_sval==1)&&(val<eval&&enable_eval==1))
                                {
                                    checked=true;
                                }
                            }
                            else if(field.equals("yearflow")&&yearflow!=Double.MIN_VALUE)
                            {
                                val=yearflow;
                                if((val>=sval&&enable_sval==1)&&(val<eval&&enable_eval==1))
                                {
                                    checked=true;
                                }
                            }
                            else if(field.equals("monthflowrate")&&yearflow!=Double.MIN_VALUE)
                            {
                                val=monthflow*100/(jsonUtil.get_double_by_path(row_plan.toJSONString(),"$.plan"+(now_date.substring(5,7))));
                                if((val>=sval&&enable_sval==1)&&(val<eval&&enable_eval==1))
                                {
                                    checked=true;
                                }
                            }
                            else if(field.equals("yearflowrate")&&yearflow!=Double.MIN_VALUE)
                            {
                                val=yearflow*100/(limit);
                                if((val>=sval&&enable_sval==1)&&(val<eval&&enable_eval==1))
                                {
                                    checked=true;
                                }
                            }
                            if(checked)
                            {
                                //如果不存在就新增
                                if(rows_alarm.size()==0)
                                {
                                    Map<String,Object> row=new HashMap<>();
                                    row.put("alarmconfig_id",id);
                                    row.put("device_id",device_id);
                                    row.put("type",type);
                                    row.put("field",field);
                                    row.put("ftime",(new Date().getTime()));
                                    row.put("etime",0);
                                    row.put("fval",val);
                                    row.put("state",0);
                                    row.put("deleted",0);
                                    dbService.save(rows_column,"alarm",row);
                                }
                            }
                            else {
                                //如果存在就结束
                                if(rows_alarm.size()>0)
                                {
                                    int alarm_id=Integer.parseInt(rows_alarm.get(0).get("id")+"");
                                    Map<String,Object> row=new HashMap<>();
                                    row.put("id",alarm_id);
                                    row.put("eval",val);
                                    row.put("etime",now.getTime());
                                    row.put("edate",common.format_date(now));
                                    row.put("state",1);
                                    row.put("deleted",0);
                                    dbService.save(rows_column,"alarm",row);
                                }
                            }
                        }
                        fileUtil.add_msg("do_alarm",device_id+":"+id);
                    }
                    else {
                        fileUtil.add_msg("do_alarm","不匹配:"+device_id+":"+id);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            log.info(ex.toString());
            fileUtil.add_msg("do_alarm",common.format_exception(ex));
        }
    }
}
